﻿using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Windows.Controls;
using Inspire.Classes.DataStructures;
using Inspire.Classes.Interfaces;
using Inspire.DataStructures;
using Inspire.Entities;
using Inspire.Utility;
using System.Text;
using System.Data.Objects;
using System.Data.Common;
using System.Data.SqlClient;
using System;
using System.ComponentModel;
using System.Windows;
using System.Data;
using Inspire.Windows;
using Inspire.Classes.Utility;
using Inspire.Classes.Exceptions;
using Inspire.Classes.Common;
using Inspire.Classes.Business;

namespace Inspire.Pages
{
    /// <summary>
    /// Interaction logic for SearchPatrons.xaml
    /// </summary>
    public partial class SearchPatrons : Page, IPage, INotifyPropertyChanged
    {
        /// <summary>
        /// Get a reference to our persistence context.
        /// </summary>
        private InspireEntities _context = ContextProvider.GetContextProvider().GetContext();

        /// <summary>
        /// Get a reference to the PatronBusiness class.
        /// </summary>
        private PatronBusiness _patronBusiness = PatronBusiness.GetPatronBusiness();

        // Make the Field Names list avaialable
        private ObservableCollection<KeyValue<string,string>> fieldNames = new ObservableCollection<KeyValue<string,string>>();
        public ObservableCollection<KeyValue<string, string>> FieldNames
        {
            get
            {
                return this.fieldNames;
            }
        }

        // Make the acceptable Search Operators list available
        public string[] SearchOperators
        {
            get
            {
                return SearchCriteria.searchOperators;
            }
        }

        // Make the acceptable Comparison Operators list available
        public string[] ComparisonOperators
        {
            get
            {
                return SearchCriteria.comparisonOperators;
            }
        }

        // Create the list backing the Search Criteria data grid
        private ObservableCollection<SearchCriteria> _criteriaList = new ObservableCollection<SearchCriteria>();
        public ObservableCollection<SearchCriteria> CriteriaList
        {
            get
            {
                return this._criteriaList;
            }
        }

        /// <summary>
        /// Results of the query.
        /// </summary>
        private ObjectResult<Patron> _resultSet;

        /// <summary>
        /// Displays the number of matching results to the user
        /// </summary>
        private string _queryResultsMessage = "Your Query Returned [0] Results";
        public string QueryResultsMessage
        {
            get
            {
                return this._queryResultsMessage;
            }
            set
            {
                this._queryResultsMessage = value;
                this.NotifyPropertyChanged("QueryResultsMessage");
            }
        }

        /// <summary>
        /// Backs the results pageIndex.
        /// </summary>
        private ObservableCollection<Patron> _resultList = new ObservableCollection<Patron>();
        public ObservableCollection<Patron> ResultList
        {
            get
            {
                return this._resultList;
            }
        }

        /// <summary>
        /// Handles paginating our results from the query.
        /// </summary>
        private Paginator<Patron> _paginator;

        public SearchPatrons()
        {
            InitializeComponent();

            // Build our list of available field types
            this.fieldNames.Add(new KeyValue<string, string>("address1", "Address 1"));
            this.fieldNames.Add(new KeyValue<string, string>("address2", "Address 2"));
            this.fieldNames.Add(new KeyValue<string, string>("city", "City"));
            this.fieldNames.Add(new KeyValue<string, string>("comments", "Comments"));
            this.fieldNames.Add(new KeyValue<string, string>("company", "Company"));
            this.fieldNames.Add(new KeyValue<string, string>("country", "Country"));
            this.fieldNames.Add(new KeyValue<string, string>("fname", "First Name"));
            this.fieldNames.Add(new KeyValue<string, string>("lname", "Last Name"));
            this.fieldNames.Add(new KeyValue<string, string>("mname", "Middle Name"));
            this.fieldNames.Add(new KeyValue<string, string>("postalCode", "Postal Code"));
            this.fieldNames.Add(new KeyValue<string, string>("readingLevel", "Reading Level"));
            this.fieldNames.Add(new KeyValue<string, string>("school", "School"));
            this.fieldNames.Add(new KeyValue<string, string>("state", "State"));
            this.fieldNames.Add(new KeyValue<string, string>("studentGrade", "Student Grade"));
            this.fieldNames.Add(new KeyValue<string, string>("studentId", "Student ID"));
        }

        private void btnAddCriteria_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            /*
             * Create a new SearchCriteria and add it to the DataGrid
             */
            SearchCriteria newCriteria = new SearchCriteria();
            newCriteria.SearchOperator = "And";
            // TODO: Set default Field value based on database fields
            newCriteria.ComparisonOperator = "Is Equal To";

            this._criteriaList.Add(newCriteria);
        }

        private void btnRemoveCriteria_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            /*
             * Remove the Selected Row if it exists
             */
            if (this.dgSearchCriteria.SelectedCells.Count > 0)
            {
                DataGridCell cell = DataGridHelper.GetCell(this.dgSearchCriteria.SelectedCells[0]);
                this._criteriaList.Remove((SearchCriteria)DataGridHelper.GetRowItem(cell));
            }
        }

        #region IPage Members

        /// <summary>
        /// Notification that this pageIndex is displayed to the user.
        /// </summary>
        public void Page_Displayed()
        {
            return;
        }

        #endregion

        /// <summary>
        /// Handle validation and executing the query.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="args"></param>
        private void btnExecuteQuery_Click(object sender, System.Windows.RoutedEventArgs args)
        {
            StringBuilder errorMessage  = new StringBuilder(),
                          query         = new StringBuilder();
            SearchCriteria criteria;
            Object[] queryParams = new Object[this._criteriaList.Count];
            LinkedList<Patron> page1Results;
            LinkedListNode<Patron> nextNode;

            int i = 0;

            // Verify there are search criteria
            if (this._criteriaList.Count < 1)
            {
                errorMessage.Append("You must add at least 1 search criteria to execute a query.");
            }

            // Validate all of the search criteria
            for (i = 0; i < this._criteriaList.Count; i++)
            {
                SearchCriteria sc = this._criteriaList[i];

                if (!(sc.Validate()))
                {
                    foreach (string errorMsg in sc.GetAllErrors())
                    {
                        errorMessage.Append("Row [" + (i + 1) + "]: " + errorMsg + "\n");
                    }
                }
            }

            // If there are any errors, show them
            if (errorMessage.Length > 0)
            {
                System.Windows.MessageBox.Show(errorMessage.ToString(), "Validation Error", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
                return;
            }

            // There are no errors, so let's build a query.
            query.Append("SELECT " + 
                            "p2.id AS Id, " +
                            "p2.fname AS FirstName," +
                            "p2.lname AS LastName," +
                            "p2.address1 AS Address1," +
                            "p2.address2 AS Address2," +
                            "p2.city AS City," +
                            "p2.state AS State," +
                            "p2.country AS Country," +
                            "p2.postalCode AS PostalCode," +
                            "p2.notes AS Notes," +
                            "p2.mname AS MiddleName, " +
                            "p1.company AS Company, " +
                            "p1.studentId AS StudentId, " +
                            "p1.studentGrade AS StudentGrade, " +
                            "p1.parentId AS ParentId, " +
                            "p1.parent2Id AS ParentId2, " + 
                            "p1.school AS School, " +
                            "p1.readinglevel AS ReadingLevel " +
                            "FROM patron AS p1 INNER JOIN person AS p2 ON p1.id = p2.id WHERE ");

            // Iterate through the criteria and build the query
            for (i = 0; i < this._criteriaList.Count; i++)
            {
                // Get a reference to the criteria
                criteria = this._criteriaList[i];

                // If we've passed the first criteria, add the boolean operator
                if (i > 0)
                {
                    if (criteria.SearchOperator.Equals("And"))
                    {
                        query.Append("AND ");
                    }
                    else
                    {
                        query.Append("OR ");
                    }
                }

                // If this is the first criteria, or the last criteria ended a group, begin a new group
                if (i == 0 || this._criteriaList[(i - 1)].EndGroup)
                {
                    query.Append('(');
                }

                // Add the field name
                query.Append(criteria.FieldName.Key + ' ');

                // Add the comparison operator and the search value

                // If the comparison is "Is Equal To"
                if (criteria.ComparisonOperator.Equals(SearchCriteria.comparisonOperators[0]))
                {
                    // Is it the negated version?
                    if (criteria.Not)
                    {
                        query.Append("<> {" + i.ToString() + "} ");
                    }
                    else
                    {
                        query.Append("= {" + i.ToString() + "} ");
                    }

                    // Add a new parameter to the parameter list for this search value
                    queryParams[i] = criteria.SearchValue;
                }

                // If the comparison is "Contains"
                if (criteria.ComparisonOperator.Equals(SearchCriteria.comparisonOperators[1]))
                {
                    // Is it the negated version?
                    if (criteria.Not)
                    {
                        query.Append("NOT LIKE {" + i.ToString() + "} ");
                    }
                    else
                    {
                        query.Append("LIKE {" + i.ToString() + "} ");
                    }

                    // Add a new parameter to the parameter list for this search value
                    queryParams[i] = "%" + criteria.SearchValue + "%";
                }

                // If the comparison is "Begins With"
                if (criteria.ComparisonOperator.Equals(SearchCriteria.comparisonOperators[2]))
                {
                    // Is it the negated version?
                    if (criteria.Not)
                    {
                        query.Append("NOT LIKE {" + i.ToString() + "} ");
                    }
                    else
                    {
                        query.Append("LIKE {" + i.ToString() + "} ");
                    }

                    // Add a new parameter to the parameter list for this search value
                    queryParams[i] = criteria.SearchValue + "%";
                }

                // If the comparison is "Ends With"
                if (criteria.ComparisonOperator.Equals(SearchCriteria.comparisonOperators[3]))
                {
                    // Is it the negated version?
                    if (criteria.Not)
                    {
                        query.Append("NOT LIKE {" + i.ToString() + "} ");
                    }
                    else
                    {
                        query.Append("LIKE {" + i.ToString() + "} ");
                    }

                    // Add a new parameter to the parameter list for this search value
                    queryParams[i] = "%" + criteria.SearchValue;
                }

                // If this is the last criteria, or the end of the current group, add the cloing parentheses
                if(i == (this._criteriaList.Count - 1) || criteria.EndGroup)
                {
                    query.Append(") ");
                }
            }
            
            // Order the Results
            query.Append("ORDER BY lname, fname");

            // Execute the query
            this._resultSet = this._context.ExecuteStoreQuery<Patron>(query.ToString(), "People", MergeOption.NoTracking, queryParams);
            
            // Update the Result Set that backs the results pageIndex
            this._resultList.Clear();
            try
            {
                this._paginator = new Paginator<Patron>(this._resultSet, 20);
                page1Results = this._paginator.MoveToPage(0);
                nextNode = page1Results.First;
                while(nextNode != null)
                {
                    this._resultList.Add(nextNode.Value);
                    nextNode = nextNode.Next;
                }
            }
            catch (ResultsOutOfBoundsException e)
            {
                Console.Out.WriteLine(e.Message);
            }

            // Update the Results Message
            this.QueryResultsMessage = "Your Query Returned [" + this._paginator.TotalResults + "] ";
            if (this._resultList.Count == 1)
            {
                this.QueryResultsMessage += "Result";
            }
            else
            {
                this.QueryResultsMessage += "Results";
            }

            // Update the IsEnabled property of the pagination buttons
            this.btnPrevious.IsEnabled = false;
            this.btnNext.IsEnabled = false;
            if (this._paginator.TotalPages > 1)
            {
                this.btnNext.IsEnabled = true;
            }

            // Update the page numbers label
            this.lblPageNumbers.Content = "1 of " + this._paginator.TotalPages.ToString();

            // Move the user to the results pageIndex
            this.tabMain.SelectedIndex = 1;
        }

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #endregion

        #region Edit Selected Patron
        
        /// <summary>
        /// Handle editing the selected Patron when the user double-clicks a row in the data grid.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void DataGrid_MouseDoubleClick(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            IInputElement element = e.MouseDevice.DirectlyOver;
            if (element != null && element is FrameworkElement)
            {
                if (((FrameworkElement)element).Parent is DataGridCell)
                {
                    var grid = sender as DataGrid;
                    if (grid != null && grid.SelectedItems != null && grid.SelectedItems.Count == 1)
                    {
                        // Get a reference to the selected Patron
                        Patron selectedPatron = (Patron)grid.SelectedItem;

                        // Get a fresh copy of the object before proceeding
                        EditPatron editingWindow = new EditPatron(this._patronBusiness.GetPatron(selectedPatron.Id));
                        editingWindow.Owner = Application.Current.MainWindow;
                        editingWindow.Owner.IsEnabled = false;
                        editingWindow.ShowDialog();
                    }
                }
            }
        }

        /// <summary>
        /// Handled editing the selected Patron when the Edit Selection button is clicked.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void EditSelection_Click(object sender, RoutedEventArgs e)
        {
            if (dgResultList.SelectedItem != null)
            {
                Patron selectedPatron = (Patron)dgResultList.SelectedItem;

                // Get a fresh copy of the object before proceeding
                EditPatron editingWindow = new EditPatron(this._patronBusiness.GetPatron(selectedPatron.Id));
                editingWindow.Owner = Application.Current.MainWindow;
                editingWindow.Owner.IsEnabled = false;
                editingWindow.ShowDialog();
            }
        }

        #endregion

        #region Pagination Buttons Event Handlers

        /// <summary>
        /// Handle clicks made on the "Previous" button
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnPrevious_Click(object sender, RoutedEventArgs args)
        {
            if (this._paginator.PageNumber > 0)
            {
                // Clear the current page of items and load a new page
                this._resultList.Clear();
                try
                {
                    foreach (Patron item in this._paginator.Previous())
                    {
                        this._resultList.Add(item);
                    }
                }
                catch (ResultsOutOfBoundsException e)
                {
                    Console.Out.WriteLine(e.Message);
                }

                // If the next to the last page was just loaded, re-enable the Next button
                // NOTE: We subtract 2 because PageNumber is zero-based
                if (this._paginator.PageNumber == (this._paginator.TotalPages - 2))
                {
                    this.btnNext.IsEnabled = true;
                }
                
                // If the first page was just loaded, disable this button
                // NOTE: We test for 0 because PageNumber is zero-based
                if (this._paginator.PageNumber == 0)
                {
                    this.btnPrevious.IsEnabled = false;
                }

                // Update the Page Numbers
                this.lblPageNumbers.Content = (this._paginator.PageNumber + 1).ToString() + " of " + this._paginator.TotalPages.ToString();
            }
        }

        /// <summary>
        /// Handle clicks made on the "Next" button
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnNext_Click(object sender, RoutedEventArgs args)
        {
            // NOTE: We subtract 1 because PageNumber is zero-based
            if (this._paginator.PageNumber < (this._paginator.TotalPages - 1))
            {
                // Clear the current page of items and load a new page
                this._resultList.Clear();
                try
                {
                    foreach (Patron item in this._paginator.Next())
                    {
                        this._resultList.Add(item);
                    }
                }
                catch (ResultsOutOfBoundsException e)
                {
                    Console.Out.WriteLine(e.Message);
                }

                // If the second page was just loaded, re-enable the Previous button
                // NOTE: We use 1 instead of 2 because PageNumber is zero-based
                if (this._paginator.PageNumber == 1)
                {
                    this.btnPrevious.IsEnabled = true;
                }

                // If the last page was just reached, disable this button
                // NOTE: We subtract 1 from TotalPages because PageNumber is zero-based
                if (this._paginator.PageNumber == (this._paginator.TotalPages - 1))
                {
                    this.btnNext.IsEnabled = false;
                }

                // Update the Page Numbers
                this.lblPageNumbers.Content = (this._paginator.PageNumber + 1).ToString() + " of " + this._paginator.TotalPages.ToString();
            }
        }

        #endregion
    }
}
